package com.mylg.craw.house.command;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.HashBasedTable;
import com.google.common.collect.Table;
import com.mylg.craw.house.util.JacksonUtils;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.nio.file.DirectoryStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 合并excel
 */
//@Service
public class ExcelCombineCommand implements CommandLineRunner {


    @Override
    public void run(String... args) throws Exception {
        Path directoryPath = Paths.get("parse"); // 替换为你的目录路径
        List<OneSheet> sheets = new ArrayList<>();
        try (DirectoryStream<Path> directoryStream = Files.newDirectoryStream(directoryPath)) {
            for (Path path : directoryStream) {
                sheets.addAll(files(path));
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        write(sheets, "格式化数据剩余.xlsx");
        // files("/Users/jiangqiaonan/Downloads/2019年9月份郑州市房地产市场销售数据_1.xlsx");
        System.exit(0);
    }

    private List<OneSheet> files(Path path) {
        String filename = path.getFileName().toString();

        final YearMonth yearMonth = YearMonth.parse(filename.substring(0, filename.indexOf("月")), DateTimeFormatter.ofPattern("yyyy年M"));
        ExcelListener listener = new ExcelListener();
        listener.apply = sheetName -> {
            if (!sheetName.startsWith("sheet")) {
                return sheetName;
            }
            int index = Integer.parseInt(sheetName.substring(5));
            return yearMonth.plusMonths(index / 2).format(DateTimeFormatter.ofPattern("yyyy-MM"));
        };
        EasyExcel.read(path.toAbsolutePath().toString(), listener).doReadAll();
        return listener.getDatas();
    }

    private void write(List<OneSheet> all, String output) {
        if (all.isEmpty()) {
            return;
        }
        Map<String, List<OneSheet>> groups = all.stream().collect(Collectors.groupingBy(OneSheet::getType));
        ExcelWriter writer = EasyExcel.write(output).build();

        for (Map.Entry<String, List<OneSheet>> entry : groups.entrySet()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(entry.getKey()).build();
            Set<String> titles = new HashSet<>();
            List<List<String>> heads = new ArrayList<>();
            heads.add(Collections.singletonList("时间"));
            Table<String, String, Integer> index = HashBasedTable.create();
            List<Map<Integer, String>> data = new ArrayList<>();
            for (OneSheet oneSheet : entry.getValue()) {
                Map<Integer, String> oneItem = new HashMap<>();
                oneItem.put(0, oneSheet.getSheetName());
                Table<String, String, String> regionDetail = oneSheet.getRegionDetail();
                for (String column : regionDetail.columnKeySet()) {
                    for (String row : regionDetail.rowKeySet()) {
                        String uniqKey = String.format("%s(%s)", column, row);
                        if (titles.add(uniqKey)) {
                            heads.add(Collections.singletonList(uniqKey));
                            index.put(row, column, titles.size());
                        }
                        int currentIndex = index.get(row, column);
                        oneItem.put(currentIndex, regionDetail.get(row, column));
                    }
                }
                data.add(oneItem);
            }
            writeSheet.setHead(heads);
            writer.write(data, writeSheet);
        }
        writer.close();
    }
}
